package db_utils

import (
	"database/sql"
	"dgo/goutils/utils"
	"errors"
	"fmt"
	"net"
	"strings"
	"time"
)

var (
	DBConnBlock *utils.BlockCounter = nil
)

func ConfigDBConnBlock(max int32) {
	DBConnBlock = utils.NewBlockCounter(max)
}

type DBConn struct {
	block       *utils.BlockItem
	db          *sql.DB
	dbchangedna *int32
	dbdna       int32

	currTx                *sql.Tx
	drivername            string
	dbtype                string
	lastRows              [][]SqlValue // 暂时没有使用
	lastOperaErr          error        // query或者exec的Error
	queryfunc             func(query string, args ...interface{}) (*sql.Rows, error)
	execfunc              func(query string, args ...interface{}) (sql.Result, error)
	CloseDBFlag           int // 0不关闭DB
	OnAfterExecuteSQLFunc func(sql string, err error, args ...interface{})

	OnExecSQLFunc func(sql *string, args ...interface{})
	OnQrySQLFunc  func(sql *string, args ...interface{})
}

func IsDBError(err error) bool {
	if err == nil {
		return false
	}

	{
		if _, ok := err.(*net.OpError); ok { // 网络Error
			return false
		}
	}

	// 错误类型也可以判断
	//etype := reflect.TypeOf(err)
	//fmt.Println(etype)

	return true
}

func NewDBConn(db *sql.DB) *DBConn {
	rval := &DBConn{db: db, CloseDBFlag: 1}
	rval.onCreate()
	rval.Resetfunc()
	return rval
}

func NewDBConnWithDriverName(db *sql.DB, drivername string, dbtype string) *DBConn {
	rval := &DBConn{db: db, drivername: drivername, dbtype: dbtype, CloseDBFlag: 1}
	rval.onCreate()
	rval.Resetfunc()
	return rval
}

func (this *DBConn) IsClosed() bool {
	return *this.dbchangedna != this.dbdna
}

func (this *DBConn) ConfigChangeDNA(addr *int32) {
	this.dbchangedna = addr
	this.dbdna = *addr
}

func (this *DBConn) onCreate() {
	block := DBConnBlock
	if block != nil {
		this.block = block.BeginUse2()
	}
}

func (this *DBConn) DriverName() string {
	return this.drivername
}

func (this *DBConn) DbType() string {
	return this.dbtype
}

func (this *DBConn) Ping() error {
	return this.db.Ping()
}

func (this *DBConn) Resetfunc() {
	this.queryfunc = this.queryWithDB
	this.execfunc = this.execWithDB
}

func (this *DBConn) LastOperaError() error {
	return this.lastOperaErr
}

func (this *DBConn) AutoDoneTrans() error {
	if this.currTx == nil {
		return nil
	}
	if this.lastOperaErr != nil {
		return this.RollBack()
	} else {
		return this.Commit()
	}
}

func (this *DBConn) BeginTrans() error {
	tx, err := this.db.Begin()
	if err != nil {
		return err
	}
	this.currTx = tx
	this.queryfunc = this.queryWithTx
	this.execfunc = this.execWithTx
	return nil
}

func (this *DBConn) RollBack() error {
	this.Resetfunc()
	tx := this.currTx
	if tx == nil {
		return errors.New("None tx!")
	}
	this.currTx = nil
	return tx.Rollback()
}

func (this *DBConn) Commit() error {
	this.Resetfunc()
	tx := this.currTx
	if tx == nil {
		return errors.New("None tx!")
	}
	this.currTx = nil

	return tx.Commit()
}

func (this *DBConn) Close() error {
	if this.currTx != nil {
		this.RollBack()
	}
	if this.block != nil {
		this.block.EndUse()
		this.block = nil
	}
	if this.CloseDBFlag == 1 {
		return this.db.Close()
	}
	return nil
}

func (this *DBConn) Query(query string, args ...interface{}) (*sql.Rows, error) {

	if this.block != nil {
		this.block.DebugInfo = fmt.Sprintf("[%s]:%s", utils.NowString(), query)
	}
	rval, err := this.queryfunc(query, args...)
	this.lastOperaErr = err
	if this.OnAfterExecuteSQLFunc != nil {
		this.OnAfterExecuteSQLFunc(query, err, args...)
	}
	return rval, err
}

func (this *DBConn) queryWithTx(query string, args ...interface{}) (*sql.Rows, error) {
	if this.OnQrySQLFunc != nil {
		this.OnQrySQLFunc(&query, args...)
	}
	return this.currTx.Query(query, args...)
}

func (this *DBConn) queryWithDB(query string, args ...interface{}) (*sql.Rows, error) {
	if this.OnQrySQLFunc != nil {
		this.OnQrySQLFunc(&query, args...)
	}
	return this.db.Query(query, args...)
}

func (this *DBConn) Exec(query string, args ...interface{}) (sql.Result, error) {
	if this.block != nil {
		this.block.DebugInfo = fmt.Sprintf("[%s]:%s", utils.NowString(), query)
	}

	rval, err := this.execfunc(query, args...)
	this.lastOperaErr = err
	if this.OnAfterExecuteSQLFunc != nil {
		this.OnAfterExecuteSQLFunc(query, err, args...)
	}
	return rval, err
}

func (this *DBConn) execWithTx(query string, args ...interface{}) (sql.Result, error) {
	if this.OnExecSQLFunc != nil {
		this.OnExecSQLFunc(&query, args...)
	}
	return this.currTx.Exec(query, args...)
}

func (this *DBConn) execWithDB(query string, args ...interface{}) (sql.Result, error) {
	if this.OnExecSQLFunc != nil {
		this.OnExecSQLFunc(&query, args...)
	}
	return this.db.Exec(query, args...)
}

func (this *DBConn) ExecSqlsWithSpliter(sqlscripts string, spliter string) (int64, string, error) {
	sqls := strings.Split(sqlscripts, spliter)
	return this.ExecuteBatchSQL(sqls...)
}

func (this *DBConn) OpenProc(procname string, args string) (*sql.Rows, string, error) {
	var lvsql string = ""
	if this.dbtype == "mysql" {
		lvsql = fmt.Sprintf("call %s (%s)", procname, args)
	} else if this.dbtype == "mssql" {
		lvsql = fmt.Sprintf("exec %s %s", procname, args)
	} else {
		return nil, "", fmt.Errorf("unkown drivername:%s for DBConn.OpenProc %s %s", this.dbtype, procname, args)
	}
	rows, err := this.Query(lvsql)
	return rows, lvsql, err
}

func (this *DBConn) ExecSql(sql string, args ...interface{}) (int64, error) {
	res, err := this.Exec(sql, args...)
	if err != nil {
		return 0, err
	} else {
		affect, err := res.RowsAffected()
		return affect, err
	}
}

/*
*

	n 为执行了Range多少次
*/
func (this *DBConn) RangeRows(rowRange func(row []SqlValue, err error) bool, sql string, args ...interface{}) (int, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	n := 0
	for rows.Next() {
		rowValues, err := GetRowValues(rows)
		n++
		if !rowRange(rowValues, err) {
			break
		}

	}
	return n, nil
}

/*
*

	n 为执行了Range多少次
*/
func (this *DBConn) RangeRowsEx(rowRange func(ridx int, row []SqlValue, cols []string, err error) bool, sql string, args ...interface{}) (int, error) {
	rows, cols, err := this.GetRowListWithColumns(sql, args...)
	if err != nil {
		return 0, err
	}

	n := 0
	for i := 0; i < len(rows); i++ {
		rowValues := rows[i]
		n++
		if !rowRange(i, rowValues, cols, err) {
			break
		}

	}
	return n, nil
}

func (this *DBConn) GetRowList(sql string, args ...interface{}) ([][]SqlValue, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	rval := make([][]SqlValue, 0, 1024)

	for rows.Next() {
		rowValues, err := GetRowValues(rows)
		if err != nil {
			return rval, err
		}
		rval = append(rval, rowValues)
	}

	this.lastRows = rval
	return rval, nil
}

func (this *DBConn) GetFirstValAsStr(def string, sql string, args ...interface{}) string {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return def
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := GetRowValues(rows)
		if err != nil {
			return def
		} else {
			return rowValues[0].AsString(def)
		}
	}
	return def
}

func (this *DBConn) GetFirstValAsInt64(def int64, sql string, args ...interface{}) int64 {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return def
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := GetRowValues(rows)
		if err != nil {
			return def
		} else {
			return rowValues[0].AsInt64(def)
		}
	}
	return def
}

func (this *DBConn) GetFirstVal(sql string, args ...interface{}) (*SqlValue, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := GetRowValues(rows)
		return &rowValues[0], err

	}

	return nil, nil
}

/**
 * 获取一列的所有值
 */
func (this *DBConn) GetColumnIdxVals(idx int, sql string, args ...interface{}) ([]SqlValue, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	rval := make([]SqlValue, 0)

	for rows.Next() {
		rowValues, err := GetRowValues(rows)
		if err != nil {
			return rval, err
		}
		rval = append(rval, rowValues[idx])
	}
	return rval, nil
}

/***
 * sql语句只能有一个字段值输出(Scan的方式)
 *    返回的是第一列的所有值
 */
func (this *DBConn) GetFirstColAsStringList(sql string, args ...interface{}) ([]string, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()
	rval := make([]string, 0, 1024)

	var val string
	for rows.Next() {
		err := rows.Scan(&val)
		if err != nil {
			return rval, err
		}
		rval = append(rval, val)
	}

	return rval, nil
}

/***
 * sql语句只能有一个字段值输出(Scan的方式)
 */
func (this *DBConn) GetFirstColAsTimeList(sql string, args ...interface{}) ([]time.Time, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()
	rval := make([]time.Time, 0, 1024)

	var val time.Time
	for rows.Next() {
		err := rows.Scan(&val)
		if err != nil {
			return rval, err
		}
		rval = append(rval, val)
	}

	return rval, nil
}

/***
 * sql语句只能有一个字段值输出(Scan的方式)
 */
func (this *DBConn) GetFirstColAsInt64List(sql string, args ...interface{}) ([]int64, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()
	rval := make([]int64, 0, 1024)

	var val int64
	for rows.Next() {
		err := rows.Scan(&val)
		if err != nil {
			return rval, err
		}
		rval = append(rval, val)
	}

	return rval, nil
}

func (this *DBConn) GetRowCount(sql string, args ...interface{}) (int, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	r := 0

	for rows.Next() {
		r++
	}

	return r, nil
}

func (this *DBConn) GetFirstRowWithColumns(sql string, args ...interface{}) ([]SqlValue, []string, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := GetRowValues(rows)
		columns, _ := rows.Columns()
		return rowValues, columns, err
	}
	return nil, nil, nil
}

func (this *DBConn) GetFirstRow(sql string, args ...interface{}) ([]SqlValue, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := GetRowValues(rows)
		return rowValues, err

	}

	return nil, nil
}

func (this *DBConn) GetColumnsByTable(strTable string) ([]string, error) {
	return this.GetColumns(
		fmt.Sprintf("select * from %s where 1=0", strTable))
}

func (this *DBConn) GetColumns(sql string, args ...interface{}) ([]string, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return rows.Columns()
}

func (this *DBConn) GetRowListWithColumns(sql string, args ...interface{}) ([][]SqlValue, []string, error) {
	rows, err := this.Query(sql, args...)
	if err != nil {
		return nil, nil, err
	}

	defer rows.Close()

	rval := make([][]SqlValue, 0, 1024)
	columns, _ := rows.Columns()
	for rows.Next() {
		rowValues, err := GetRowValues(rows)
		if err != nil {
			return rval, columns, err
		}
		rval = append(rval, rowValues)
	}

	return rval, columns, nil
}

func (this *DBConn) ExecuteBatchSQLWithTrans(sqls ...string) (int64, string, error) {
	this.BeginTrans()
	r, errsql, err := this.ExecuteBatchSQL(sqls...)
	if err != nil {
		this.RollBack()
	} else {
		this.Commit()
	}
	return r, errsql, err
}

func (this *DBConn) ExecuteBatchSQL(sqls ...string) (int64, string, error) {
	var r int64 = 0
	for _, v := range sqls {
		v = utils.Trim(v)
		if len(v) > 0 {
			r1, err := this.ExecSql(v)
			if err != nil {
				return -1, v, err
			} else {
				r += r1
			}
		}
	}
	return r, "", nil
}

func (this *DBConn) ExecuteBatchSQLEx(onErrCb func(idx int, sql string, err error) bool, sqls ...string) int64 {
	var r int64 = 0
	for k, v := range sqls {
		v = utils.Trim(v)
		if len(v) > 0 {
			r1, err := this.ExecSql(v)
			if err != nil {
				if onErrCb != nil {
					if !onErrCb(k, v, err) {
						break
					}
				}
			} else {
				r += r1
			}
		}
	}
	return r
}

func (this *DBConn) ExecuteNextSQL(getsql func(idx int, preRowsAffected int64, preSelData [][]SqlValue) (sqltype int, sql string)) (int64, string, error) {
	idx := 0
	var lastsql string
	var preRowsAffected int64 = 0
	var preSelData [][]SqlValue = nil
	var err error = nil
	for {
		sqltype, lvsql := getsql(idx, preRowsAffected, preSelData)
		if len(lvsql) == 0 {
			break
		}
		lastsql = lvsql
		if sqltype == 0 {
			preRowsAffected, err = this.ExecSql(lvsql)
		} else {
			rows, err1 := this.GetRowList(lvsql)
			preRowsAffected = int64(len(rows))
			err = err1
			preSelData = rows
		}
		if err != nil {
			return -1, lvsql, err
		}
		idx++
	}
	return preRowsAffected, lastsql, nil
}

// 不存在就进行更新
func (this *DBConn) ExecuteUpOrInsertByRecUp(recUp *RecUpdateUpbuilder) (int64, string, error) {
	return this.ExecuteUpOrInsertByDB(recUp, recUp.KeyFields, recUp.Uptablename)
}

// 不存在就进行更新
func (this *DBConn) ExecuteUpOrInsertByDB(recUp *RecUpdateUpbuilder, keyfields, tablename string) (int64, string, error) {
	_, upcnt, sql, err := this.ExecuteUpOrInsertByDBEx(recUp, keyfields, tablename)
	return upcnt, sql, err
}

func (this *DBConn) ExecuteUpOrInsertByDBEx(recUp *RecUpdateUpbuilder, keyfields, tablename string) (uptype int, upcnt int64, sql string, err error) {

	n, lvsql, err1 := this.ExecuteNextSQL(func(idx int, preRowsAffected int64, preSelData [][]SqlValue) (sqltype int, sql string) {
		if err != nil {
			return 0, ""
		}
		if idx == 0 {
			str := recUp.BuildWhere(keyfields)
			if len(str) == 0 {
				err = fmt.Errorf("需要指定更新主键的值")
				return 0, ""
			}
			return 1, fmt.Sprintf("select 1 as id from %s where %s", tablename,
				recUp.BuildWhere(keyfields))
		} else if idx == 1 {
			if preRowsAffected == 0 {
				uptype = ACTION_APPEND
				return 0, recUp.BuildInsert(tablename)
			} else {
				uptype = ACTION_UPDATE
				return 0, recUp.BuildUpdate(tablename, keyfields)
			}
		} else {
			return 0, ""
		}
	})

	if err != nil {
		return uptype, 0, lvsql, err
	} else {
		return uptype, n, lvsql, err1
	}
}

func (this *DBConn) CheckTableIsExists(tableName string) (int, error) {
	lvsql := fmt.Sprintf(check_table_exists, tableName)
	_, err := this.ExecSql(lvsql)
	if err != nil {
		return 0, err
	} else {
		return 1, nil
	}
}

func (this *DBConn) CheckCreateTable2(tableName string, create_sql string) (string, error) {
	_, err := this.CheckTableIsExists(tableName)
	if IsDBError(err) {
		lvsql := strings.Replace(create_sql, "$rep_table$", tableName, -1)
		sqlstrs := strings.Split(lvsql, ";")
		_, errsql, err := this.ExecuteBatchSQL(sqlstrs...)
		if err != nil {
			if strings.Contains(err.Error(), "Error 1050") { // mysql Error 1050: Table 'track_17098900736' already exists
				return "", nil
			} else {
				return errsql, err
			}
		}
		return errsql, err
	} else {
		return "", err
	}
}

func (this *DBConn) CheckCreateTable(tableName string, create_sql string) error {
	_, err := this.CheckCreateTable2(tableName, create_sql)
	return err
}

func (this *DBConn) CheckTableFieldIsExists(tableName, fieldname string) error {
	lvsql := fmt.Sprintf(check_table_field_exists, fieldname, tableName)
	_, err := this.ExecSql(lvsql)
	return err
}

func (this *DBConn) CheckCreateTableField(tableName, fieldname, createfieldsql string) (string, error) {
	err := this.CheckTableFieldIsExists(tableName, fieldname)
	if IsDBError(err) {
		_, errsql, err2 := this.ExecSqlsWithSpliter(createfieldsql, ";")
		return errsql, err2
	}
	return "", err
}

func (this *DBConn) CheckCreateTableField2(tableName, fieldname string, fieldtype, defaultval string, canbenull bool, afterfield string) (string, error) {
	err := this.CheckTableFieldIsExists(tableName, fieldname)
	if IsDBError(err) {
		lvsql := MakeAddFieldSQL(tableName, fieldname, fieldtype, defaultval, canbenull, afterfield)
		_, err2 := this.ExecSql(lvsql)
		if err2 != nil {
			return lvsql, err2
		}
		return "", nil

	}
	return "", err
}
